﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IsmsExplorer.Model.Documents;
using IsmsExplorer.Infrastructure.Helper;
using IsmsExplorer.Model.Projects;
using IsmsExplorer.Infrastructure.RepositoryFramework;
using IsmsExplorer.Model.Iso27001;
using System.Diagnostics;
using System.Data;
using IsmsExplorer.Infrastructure.Repositories.RiskRegister;

namespace IsmsExplorer.Infrastructure.Repositories.Documents {
    internal class Iso27001ControlDocumentationRepository : SqlServerRepositoryBase<Iso27001ControlDocumentation>, IIso27001ControlDocumentationRepository {
        #region Data
        #endregion

        #region Constructors
        public Iso27001ControlDocumentationRepository(IUnitOfWork unitOfWork)
            : base(unitOfWork) {
        }

        public Iso27001ControlDocumentationRepository()
            : this(null) {
        }
        #endregion

        // Need to override some of the IRepository<T> methods as this class is special and 
        // does NOT make sense to use some of the API.
        #region IRepository<T> implementation

        public override Iso27001ControlDocumentation FindBy(object id) {
            throw new NotImplementedException("");
        }

        public override IList<Iso27001ControlDocumentation> FindAll() {
            throw new NotImplementedException("");
        }

        public override Iso27001ControlDocumentation this[object id] {
            get { throw new NotImplementedException(""); }
            set { throw new NotImplementedException(""); }
        }

        #endregion IRepository<T> implementation

        #region IIso27001ControlDocumentationRepository members

        public Iso27001ControlDocumentation FindByControl(int projectID, int controlID) {
            Iso27001ControlDocumentation iso27001Docs = new Iso27001ControlDocumentation();
            iso27001Docs.ProjectID = projectID;
            AppendIso27001Control(iso27001Docs, controlID);
            // Get the associated documents.
            IIsmsDocumentLinkRepository r = RepositoryFactory.GetRepository<IIsmsDocumentLinkRepository, IsmsDocumentLink>();
            IList<IsmsDocumentLink> docs = r.GetDocumentsForIso27001Control(projectID, controlID);
            foreach (var doc in docs) {
                iso27001Docs.AddDocument(doc);
            }
            return iso27001Docs;
        }

        public bool ExistFor(int projectID, int controlID) {
            string sql = string.Format(@"SELECT TOP 1 {0} FROM [Iso27001ControlDocuments] WHERE {1}={2} AND {3}={4}",
                Iso27001ControlDocumentationFactory.FieldNames.DocumentID,
                Iso27001ControlDocumentationFactory.FieldNames.ProjectID,
                DataHelper.GetSqlValue(projectID),
                Iso27001ControlDocumentationFactory.FieldNames.ControlID,
                DataHelper.GetSqlValue(controlID));
            using (IDataReader reader = this.ExecuteReader(sql)) {
                return reader.Read();
            }
        }

        public Iso27001ControlDocumentation this[int projectID, int controlID] {
            get {
                return FindByControl(projectID, controlID);
            }
            set {
                // ExistFor() is faster than FindByControl(,) as it does not have to load all subobjects!
                if (!ExistFor(projectID, controlID)) {
                    Add(value);

                } else {
                    if (_unitOfWork != null) {
                        _unitOfWork.RegisterUpdated(value, this);
                    }
                }
            }
        }

        public IList<Iso27001ControlDocumentation> FindByTreatment(int projectID, int treatmentID) {
            // We want all the documents (Iso27001ControlDocumentation) given Treatment/Project ID. The way to get them is to find the
            // associated controls with the project/treatment and for each control get the relevant document.
            // NOTE: in case there is no document associated with a control yet, we must make sure we still create empty Iso27001ControlDocumentation
            //       objects and return them. Empty in the sense that they contain no actual documents (empty list) but still have the ISO control
            //       and project in them. 
            //       The query which has been commented out below is the one that was unsuccessful in that it did not cater for controls with no
            //       associated documents.
            //StringBuilder sb = new StringBuilder(GetBaseQuery());
            //sb.Append(" WHERE ")
            //    .AppendFormat("{0}={1}", Iso27001ControlDocumentationFactory.FieldNames.ProjectID, DataHelper.GetSqlValue(projectID))
            //    .Append(" AND ").Append(Iso27001ControlDocumentationFactory.FieldNames.ControlID).Append(" IN ")
            //    .AppendFormat("(SELECT {0} FROM TreatmentApplications WHERE {1}={2} AND {3}={4})",
            //    Iso27001ControlDocumentationFactory.FieldNames.ControlID,
            //    Iso27001ControlDocumentationFactory.FieldNames.ProjectID,
            //    DataHelper.GetSqlValue(projectID),
            //    "TreatmentID", // NOTE: haven'treatment got defined constants for TreatmentApplications table!
            //    DataHelper.GetSqlValue(treatmentID)
            //    );
            //
            // An example query would be: 
            // NOTE the RIGHT JOIN, which ensures Iso27001ControlDocumentation values are brought back as null if not exist!
            //
            // SELECT Docs.ProjectID, Docs.DocumentID, Tas.ControlID FROM Iso27001ControlDocuments Docs RIGHT JOIN TreatmentApplications Tas 
            // ON Docs.ControlID=Tas.ControlID AND Docs.ProjectID=Tas.ProjectID
            // WHERE Tas.ProjectID=102 AND TreatmentID=415
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"SELECT Tas.{0}, Docs.{1}, Tas.{2}",
                Iso27001ControlDocumentationFactory.FieldNames.ProjectID,
                Iso27001ControlDocumentationFactory.FieldNames.DocumentID,
                Iso27001ControlDocumentationFactory.FieldNames.ControlID)
                .Append(@" FROM Iso27001ControlDocuments Docs RIGHT JOIN TreatmentApplications Tas")
                .AppendFormat(@" ON Docs.{0}=Tas.{0} AND Docs.{1}=Tas.{1}",
                Iso27001ControlDocumentationFactory.FieldNames.ControlID,
                Iso27001ControlDocumentationFactory.FieldNames.ProjectID)
                .AppendFormat(@" WHERE Tas.{0}={1} AND {2}={3}",
                Iso27001ControlDocumentationFactory.FieldNames.ProjectID, projectID,
                "TreatmentID", treatmentID);
            return this.BuildEntitiesFromSql(sb.ToString());
        }

        #endregion

        #region BuildChildDataCallbacks
        protected override void InitialiseChildCallbacks() {
            // Need to insert callbacks to build child objects: Control.
            this.ChildCallbacks.Add(Iso27001ControlDocumentationFactory.FieldNames.ControlID, this.AppendIso27001Control);
        }
        #endregion

        #region Child object callbacks

        private void AppendIso27001Control(Iso27001ControlDocumentation isoDocumentation, object controlID) {
            IIso27001ControlRepository r = RepositoryFactory.GetRepository<IIso27001ControlRepository, Iso27001ControlBase>();
            isoDocumentation.IsoControl = r.FindControlByID((int)controlID);
            Debug.Assert(isoDocumentation.IsoControl != null);
        }
        #endregion

        #region BuildEntityFromReader override (to get the IsmsDocumentLinks associated with Iso27001ControlDocumentation).

        protected override Iso27001ControlDocumentation BuildEntityFromReader(IDataReader reader) {
            Iso27001ControlDocumentation obj = base.BuildEntityFromReader(reader);
            Debug.Assert(obj.IsoControl != null);
            Debug.Assert(obj.IsoControl.ID > 0);
            // Get the associated documents.
            IIsmsDocumentLinkRepository r = RepositoryFactory.GetRepository<IIsmsDocumentLinkRepository, IsmsDocumentLink>();
            IList<IsmsDocumentLink> docs = r.GetDocumentsForIso27001Control(obj.ProjectID, obj.IsoControl.ID);
            foreach (var doc in docs) {
                obj.AddDocument(doc);
            }
            return obj;
        }

        #endregion

        #region GetBaseQuery
        protected override string GetBaseQuery() {
            return string.Format(@"SELECT {0},{1},{2} FROM [Iso27001ControlDocuments]",
                Iso27001ControlDocumentationFactory.FieldNames.ControlID,
                Iso27001ControlDocumentationFactory.FieldNames.DocumentID,
                Iso27001ControlDocumentationFactory.FieldNames.ProjectID);
        }
        #endregion

        #region GetBaseWhereClause
        protected override string GetBaseWhereClause() {
            StringBuilder sb = new StringBuilder(" WHERE ");
            sb.Append(Iso27001ControlDocumentationFactory.FieldNames.ProjectID).Append("={0} AND ")
              .Append(Iso27001ControlDocumentationFactory.FieldNames.ControlID).Append("={1}");
            return sb.ToString();
        }
        #endregion

        protected override string BuildBaseWhereClause(object id) {
            throw new NotImplementedException("");
        }
        protected virtual string BuildBaseWhereClause(int projectID, int controlID) {
            return string.Format(GetBaseWhereClause(), projectID, controlID);
        }

        #region UnitOfWork implementation

        /// <summary>
        /// Disassociates the ISMS documents in the Documents list from the given control and project.
        /// If the Iso27001ControlDocumentation Documents list is empty, this will disassociate ALL the
        /// associated documents for the control for the given project!
        /// </summary>
        /// <param name="item"></param>
        protected override void PersistDeletedItem(Iso27001ControlDocumentation item) {
            Debug.Assert(item.IsoControl != null);
            Debug.Assert(item.IsoControl.ID > 0);
            string sql = string.Format(@"DELETE FROM [Iso27001ControlDocuments] {0}", this.BuildBaseWhereClause(item.ProjectID, item.IsoControl.ID));
            if (item.Documents.Count() > 0) {
                StringBuilder sb = new StringBuilder();
                foreach (IsmsDocumentLink doc in item.Documents) {
                    sb.Append(DataHelper.GetSqlValue(doc.DocumentID)).Append(",");
                }
                sb.Length -= ",".Length;
                sql = string.Concat(sql, " AND (", Iso27001ControlDocumentationFactory.FieldNames.DocumentID, " IN (", sb.ToString(), "))");
            }
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sql));
        }

        protected override void PersistNewItem(Iso27001ControlDocumentation item) {
            // Need to insert an association between the ISO control and each document(s) in this object.
            StringBuilder sb = new StringBuilder(256);
            sb.AppendFormat(@"INSERT INTO [Iso27001ControlDocuments]({0},{1},{2})",
                Iso27001ControlDocumentationFactory.FieldNames.ProjectID,
                Iso27001ControlDocumentationFactory.FieldNames.ControlID,
                Iso27001ControlDocumentationFactory.FieldNames.DocumentID)
            .AppendFormat(@" VALUES ({0},{1}",
                DataHelper.GetSqlValue(item.ProjectID),
                DataHelper.GetSqlValue(item.IsoControl.ID))
            .Append(@",{0});");
            foreach (IsmsDocumentLink doc in item.Documents) {
                int rowsAffected = this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(string.Format(sb.ToString(), DataHelper.GetSqlValue(doc.DocumentID))));
                Debug.Assert(rowsAffected > 0);
            }
        }

        protected override void PersistUpdatedItem(Iso27001ControlDocumentation item) {
            // Updated means there's different documents associated with the control.
            // We first delete ALL previous associations (for (control, project)) and then reinsert this (changed) item.
            Iso27001ControlDocumentation itemToDeleteAllAssociations = new Iso27001ControlDocumentation();
            itemToDeleteAllAssociations.ProjectID = item.ProjectID;
            itemToDeleteAllAssociations.IsoControl = item.IsoControl;
            Debug.Assert(itemToDeleteAllAssociations.IsoControl != null && itemToDeleteAllAssociations.IsoControl.ID > 0);
            PersistDeletedItem(itemToDeleteAllAssociations);
            PersistNewItem(item);
        }
        #endregion

        #region Private helper/callback methods
        #endregion

    } // end internal class Iso27001ControlDocumentationRepository : SqlServerRepositoryBase<Iso27001ControlDocumentation>, IIso27001ControlDocumentationRepository

}
